////////////////////////////////////////////////////////////////////////////////
// Copyright 2006 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   CODE
// AUTHOR           :   Saint Peng 
// DATE CREATE      :   2006/01/05
// DESCRIPTION      :   a DAO file for the function, CODE
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2006/01/05   Saint Peng      1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.setup.codeSetup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.common.ProjectConstants;
import com.erp.app.setup.codeSetup.pk.CodeDtlPk;
import com.erp.app.setup.codeSetup.pk.CodeHdrPk;
import com.erp.app.setup.codeSetup.vo.CodeDtlVo;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.dao.SimpleSearchMaintStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.types.CustomSqlDate;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Saint Peng 
* @version 1.0     2006/01/05
* @since           FrameWork 1.0
*
*/
public class CodeDtlDao extends SimpleSearchMaintStyleDao {

	/**
	* Returns an array list of child VOs by the parent
	* @param         parent   the incoming parent VO
	* @param         conn     the database connection
	* @return        an array list of child VOs
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
		throws FindRecordException {

		return super.selectByParent(parent, CodeDtlVo.class, conn);
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected String sqlInsert() {

		String sql = "INSERT INTO T_CODE_DTL ("
			+ "COMPANY_CODE, REF_MASTER_CODE, REF_CODE, REF_DESC, REF_STATUS, "
			+ "REF_1, REF_2, REF_3, DC_IND, DC_DATE, "
			+ "CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, SEQ) "
			+ "VALUES ("
			+ "?, ?, ?, ?, ?, "
			+ "?, ?, ?, 'NO', ?, "
			+ "?, SYSDATE, ?, ?, ?)";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL insert or update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

		CodeDtlVo vo = (CodeDtlVo) obj;
		int i = 1;

		pStmt.setString(i++, vo.getCompanyCode());
        
        if (ProjectConstants.DB_OP_INSERT.equals(opType)){
//            vo.setRefMasterCode(vo.getRefMasterCode().toUpperCase());
            vo.setRefCode(vo.getRefCode().toUpperCase());
//		    pStmt.setString(i++, vo.getRefMasterCode().toUpperCase());
//		    pStmt.setString(i++, vo.getRefCode().toUpperCase());
        }
//        if (ProjectConstants.DB_OP_UPDATE.equals(opType)){
            pStmt.setString(i++, vo.getRefMasterCode());
		    pStmt.setString(i++, vo.getRefCode());
//        }

		setNullableString(pStmt, i++, vo.getDtlRefDesc());
		setNullableString(pStmt, i++, vo.getDtlRefStatus());
		setNullableString(pStmt, i++, vo.getDtlRef1());
		setNullableString(pStmt, i++, vo.getDtlRef2());
		setNullableString(pStmt, i++, vo.getDtlRef3());

		if (ProjectConstants.DB_OP_UPDATE.equals(opType))
			setNullableString(pStmt, i++, vo.getDtlDcInd());
		setNullableDate(pStmt, i++, vo.getDtlDcDate());
		setNullableString(pStmt, i++, vo.getCreateUser());

		if (ProjectConstants.DB_OP_UPDATE.equals(opType))
			setNullableTimestamp(pStmt, i++, vo.getCreateDate());

		setNullableString(pStmt, i++, vo.getUpdateUser());

		if (ProjectConstants.DB_OP_INSERT.equals(opType))
			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
		    setNullableBigInteger(pStmt, i++, vo.getDtlSeq());

		if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
			pStmt.setString(i++, vo.getCompanyCode());
			pStmt.setString(i++, vo.getRefMasterCode());
			pStmt.setString(i++, vo.getRefCode());
			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
		}
	}

	/**
	* Sets the values for a SQL insert statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	/**
	* Constructs a string of SQL update statement
	* @return        a string of SQL update statement
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected String sqlUpdate() {

		String sql = "UPDATE T_CODE_DTL SET COMPANY_CODE=?, REF_MASTER_CODE=?, REF_CODE=?, REF_DESC=?, REF_STATUS=?, "
			+ "REF_1=?, REF_2=?, REF_3=?, DC_IND=?, DC_DATE=?, "
			+ "CREATE_USER=?, CREATE_DATE=?, UPDATE_USER=?, UPDATE_DATE=SYSDATE, SEQ=? "
			+ "WHERE COMPANY_CODE=? AND REF_MASTER_CODE=? AND REF_CODE=? "
			+ "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

	/**
	* Constructs a string of SQL delete statement
	* @return        a string of SQL update statement
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected String sqlDelete() {
		String sql = "DELETE FROM T_CODE_DTL WHERE COMPANY_CODE=? AND REF_MASTER_CODE=? AND REF_CODE=? ";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL delete statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
		throws SQLException {

		CodeDtlVo vo = (CodeDtlVo) obj;
		int i = 1;

		pStmt.setString(i++, vo.getCompanyCode());
		pStmt.setString(i++, vo.getRefMasterCode());
		pStmt.setString(i++, vo.getRefCode());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected String sqlFind() {

		String sql = "SELECT COMPANY_CODE, REF_MASTER_CODE, REF_CODE, REF_DESC, REF_STATUS, "
			+ "REF_1, REF_2, REF_3, DC_IND, DC_DATE, "
			+ "CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, SEQ "
			+ "FROM T_CODE_DTL "
			+ "WHERE COMPANY_CODE=? AND REF_MASTER_CODE=? AND REF_CODE=? "
			+ "ORDER BY COMPANY_CODE, SEQ ASC, REF_CODE ASC";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding by primary key.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming PK
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {

		CodeDtlPk pk = (CodeDtlPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setString(i++, pk.getRefMasterCode());
		pStmt.setString(i++, pk.getRefCode());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected String sqlList() {

		String sql = "SELECT COMPANY_CODE, REF_MASTER_CODE, REF_CODE, REF_DESC, REF_STATUS, "
			+ "REF_1, REF_2, REF_3, DC_IND, DC_DATE, "
			+ "CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, SEQ "
			+ "FROM T_CODE_DTL "
			+ "WHERE COMPANY_CODE=? AND REF_MASTER_CODE=? "
			+ "ORDER BY COMPANY_CODE, REF_MASTER_CODE, SEQ ASC, REF_CODE ASC";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding child tables.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming parent PK
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {

		CodeHdrPk pk = (CodeHdrPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setString(i++, pk.getRefMasterCode());
//		pStmt.setString(i++, pk.getRefCode());
	}

	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Saint Peng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

		CodeDtlVo vo = new CodeDtlVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setRefMasterCode(rs.getString("REF_MASTER_CODE"));
		vo.setRefCode(rs.getString("REF_CODE"));
		vo.setDtlRefDesc(rs.getString("REF_DESC"));
		vo.setDtlRefStatus(rs.getString("REF_STATUS"));
		vo.setDtlRef1(rs.getString("REF_1"));
		vo.setDtlRef2(rs.getString("REF_2"));
		vo.setDtlRef3(rs.getString("REF_3"));
		vo.setDtlDcInd(rs.getString("DC_IND"));
		vo.setDtlDcDate(CustomSqlDate.customize(rs.getDate("DC_DATE")));
		vo.setCreateUser(rs.getString("CREATE_USER"));
		vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
		vo.setUpdateUser(rs.getString("UPDATE_USER"));
		vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
		vo.setDtlSeq(CustomBigInteger.customize(rs.getBigDecimal("SEQ")));
		
		return vo;
	}

    /**
     * @see com.erp.framework.model.dao.SimpleBaseDao#sqlSelectByPrimaryKey()
     * 
     * @author     Ric Yik
     * @since      FrameWork 1.0
     */
    protected String sqlSelectByPrimaryKey() {
        return this.sqlFind();
    }

    /**
     * @see com.erp.framework.model.dao.SimpleBaseDao#setValuesForSelectByPrimaryKey(java.sql.PreparedStatement, com.erp.framework.model.pk.SimpleBasePk)
     * 
     * @author     Ric Yik
     * @since      FrameWork 1.0
     */
    protected void setValuesForSelectByPrimaryKey(PreparedStatement pStmt, SimpleBasePk obj) throws SQLException {
        this.setValuesForFind(pStmt, obj);
    }

    /**
     * @see com.erp.framework.model.dao.SimpleBaseDao#getModelForSelectByPrimarKey(java.sql.ResultSet)
     * 
     * @author     Ric Yik
     * @since      FrameWork 1.0
     */
    protected SimpleBaseVo getModelForSelectByPrimarKey(ResultSet rs) throws SQLException {
        return this.getResultSetValues(rs);
    }

}
